package rocket.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import rocket.calendar.Visit;
import rocket.logs.Write;
import rocket.logs.WriteType;
import rocket.medical.Record;
import rocket.medical.Treatment;
import rocket.server.Access;
import rocket.users.User;

/**
 * @author justin
 * @version
 *controls the database
 */
public class DatabaseController {

	private static final int MAX_LOGIN_ATTEMPTS = 3;

	private static DatabaseController DBC;
	
	private Statement s = null;

	private DatabaseController() {

	}

	/**
	 * Gets the singleton object DatabaseController, if it is null, create a new
	 * one.
	 * 
	 * @return a DatabaseController object
	 */
	public static synchronized DatabaseController getDBC() {
		if (DBC == null) {
			DBC = new DatabaseController();
		}

		return DBC;
	}

	/**
	 * Clears the current DatabaseController
	 * 
	 * @param access
	 */
	public static synchronized void clear(Access access) {
		DBC = null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#clone()
	 */
	@Override
	public Object clone() throws CloneNotSupportedException {
		throw new CloneNotSupportedException(
				"Database Controller cannot be cloned.");
	}

	/**
	 * Attempts to Connect to database rocket at localhost on port 3306 with a
	 * given user and password
	 * 
	 * @param user
	 *            the username to connect with
	 * @param pass
	 *            the password to connect with
	 * @return a connection to localhost:3306/rocket for 'user'@'localhost'
	 *         Identified by 'pass'
	 * @throws SQLException
	 */
	public Connection connect(String user, String pass) throws SQLException {
		Connection con = null;
		con = DriverManager.getConnection("jdbc:mysql://localhost:3306/rocket",
				user, pass);
		return con;
	}

	/**
	 * Gets the number of login attempts for a given user
	 * 
	 * @param userID
	 *            the user to get
	 * @return the number of login attempts if found, MAX_LOGIN_ATTEMPTS
	 *         otherwise
	 */
	public int getLoginAttempt(long userID) {
		int attempt = MAX_LOGIN_ATTEMPTS;
		Connection con = view();
		try {
			Statement s = con.createStatement();
			ResultSet results = s
					.executeQuery("SELECT login_attempt FROM user WHERE id="
							+ userID + ";");
			if (results != null && results.next()) {
				attempt = results.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			writeLog(e.getMessage());
		}
		return attempt;
	}

	/**
	 * Increments the loginAttempts for a given user, because they failed their
	 * login
	 * 
	 * @param userID
	 *            the user who's login should be incremented
	 */
	public void incrementLoginAttempts(long userID) {
		final int attempt = getLoginAttempt(userID) + 1;
		final Connection con = view();
		try {
			final Statement s = con.createStatement();
			s.execute("UPDATE user SET login_attempt="
					+ Math.min(MAX_LOGIN_ATTEMPTS, attempt) + " WHERE id="
					+ userID + ";");
		} catch (SQLException e) {
			e.printStackTrace();
			writeLog(e.getMessage());
		}
	}

	/**
	 * Sets a user's login attempts to a given value
	 * 
	 * @param userID
	 *            the user to set
	 * @param attempt
	 *            the number of attempts
	 */
	public void setLoginAttempts(long userID, int attempt) {
		final Connection con = view();
		try {
			final Statement s = con.createStatement();
			s.execute("UPDATE user SET login_attempt="
					+ Math.min(MAX_LOGIN_ATTEMPTS, attempt) + " WHERE id="
					+ userID + ";");
		} catch (SQLException e) {
			e.printStackTrace();
			writeLog(e.getMessage());
		}
	}

	/**
	 * Resets the loginAttempts for a given user, should only be accessed by
	 * administrative or on a successful login
	 * 
	 * @param userID
	 *            the user to reset
	 */
	public void resetLoginAttempts(long userID) {
		final Connection con = view();
		try {
			final Statement s = con.createStatement();
			s.execute("UPDATE user SET login_attempt=0 WHERE id=" + userID
					+ ";");
		} catch (SQLException e) {
			e.printStackTrace();
			writeLog(e.getMessage());
		}
	}

	/**
	 * Gets a connection able to SELECT and UPDATE in rocket.user
	 * 
	 * @return a connection to 'view'@'localhost'
	 */
	public Connection view() {
		Connection con = null;
		try {
			con = connect("view", "view");
		} catch (SQLException e) {
			e.printStackTrace();
			writeLog(e.getMessage());
		}
		return con;
	}

	/**
	 * Can create a User, Visit, or Treatment using this method. Records are not
	 * created; rather the list of Treatments are read in order to see a
	 * trainer's record.
	 * 
	 * @param access
	 *            the access level of the method caller
	 * @param o
	 *            the object to create
	 * @param con 
	 * 			  connection
	 * @return the ID of the created object, -1 if null, or -2 if invalid object
	 */
	public long create(Connection con, Access access, Object o) {
		if (o == null) {
			return -1;
		} else if (o instanceof User && Access.NURSEJOY.compareTo(access) <= 0) {
			final User user = (User) o;
			return create(con, access, user);
		} else if (o instanceof Visit && Access.NURSEJOY.compareTo(access) <= 0) {
			final Visit visit = (Visit) o;
			return create(con, access, visit);
		} else if (o instanceof Treatment && Access.PROFESSOR.equals(access)) {
			final Treatment treatment = (Treatment) o;
			return create(con, access, treatment);
		} else {
			return -2;
		}
	}

	/**
	 * Creates a user on the database with information stored within User u
	 * 
	 * @param con
	 *            a connection that can be used to create users
	 * @param access
	 *            an access with permissions to create users
	 * @param u
	 *            the information of the user to create
	 * @return the created user's ID
	 */
	private long create(Connection con, Access access, User u) {
		long userID = 0;

		// Creates entry in the user table
		final String createEntry = "INSERT INTO user ("
				+ "access,name,birth_date,gender,phone,"
				+ "city,region,pharmacy,insurance" + ") VALUES(" + "'"
				+ u.getAccess().toString().toLowerCase() + "','" + u.name()
				+ "','" + u.birthDate() + "','" + u.gender() + "','"
				+ u.phone() + "','" + u.city() + "','" + u.region() + "','"
				+ u.pharmacy() + "','" + u.insurance() + "');";

		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(createEntry, Statement.RETURN_GENERATED_KEYS);
			final ResultSet rs = s.getGeneratedKeys();
			if (rs != null && rs.next()) {
				userID = rs.getInt(1);
			} else {
				System.err.println("userID not generated properly");
			}
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		// updates the users ID
		u.setID(userID);

		// Creates a user that can log into the database
		final String createLocalUser = "CREATE USER '" + u.name().charAt(0) + userID
				+ "'@'localhost' IDENTIFIED BY '" + u.name().charAt(0) + userID
				+ "';";

		final String createGenericUser = "CREATE USER '" + u.name().charAt(0)
				+ userID + "'@'%' IDENTIFIED BY '" + u.name().charAt(0)
				+ userID + "';";

		final String localGrants = "GRANT " + u.grants() + " ON rocket.user TO '"
				+ u.name().charAt(0) + userID + "'@'localhost';";
		final String genericGrants = "GRANT " + u.grants() + " ON rocket.user TO '"
				+ u.name().charAt(0) + userID + "'@'%';";

		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(createLocalUser);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(createGenericUser);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(localGrants);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(genericGrants);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		return userID;
	}

	/**
	 * Creates a visit containing the data from v
	 * 
	 * @param con
	 * @param access
	 * @param v
	 * @return a long
	 */
	private long create(Connection con, Access access, Visit v) {
		long visitID = 0;

		String createEntry = "INSERT INTO visit ("
				+ "user_id,prof_id,nurse_id,treatment_id,visit_time,visit_location) VALUES('"
				+ v.getUserID() + "','" + v.getProfessorID() + "','"
				+ v.getNurseID() + "','" + v.getTreatmentID() + "','"
				+ v.getVisitTime() + "','" + v.getVisitLocation() + "'" + ");";

		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(createEntry, Statement.RETURN_GENERATED_KEYS);
			final ResultSet rs = s.getGeneratedKeys();
			if (rs != null && rs.next()) {
				visitID = rs.getInt(1);
			} else {
				System.err.println("visitID not generated properly");
			}
			rs.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}catch (NullPointerException e){
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		v.setID(visitID); // not working properly?
		return visitID;
	}

	/**
	 * @param con
	 * @param access
	 * @param v
	 * @return a long
	 */
	private long create(Connection con, Access access, Treatment t) {
		long treatmentID = 0;

		String createEntry = "INSERT INTO treatment ("
				+ "user_id,prof_id,nurse_id,visit_id,pokemon_id,treatment_time,"
				+ "prof_orders,symptoms,vitals,diagnosis) VALUES('"
				+ t.getUserID() + "','" + t.getProfessorID() + "','"
				+ t.getNurseID() + "','" + t.getVisitID() + "','"
				+ t.getPokemonID() + "','" + t.getTime() + "','"
				+ t.getOrders() + "','" + t.getSymptoms() + "','"
				+ t.getVitals() + "','" + t.getDiagnosis() + "');";

		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(createEntry, Statement.RETURN_GENERATED_KEYS);
			final ResultSet rs = s.getGeneratedKeys();
			if (rs != null && rs.next()) {
				treatmentID = rs.getInt(1);
			} else {
				System.err.println("treatmentID not generated properly");
			}
			rs.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		t.setID(treatmentID);
		return treatmentID;
	}
	
	/**
	 * 
	 * @param con
	 * @param access
	 * @param o
	 * @return the read. eventually -2
	 */
	public long read(Connection con, Access access, Object o) {
		if (o == null) {
			return -1;
		} else if (o instanceof User && Access.NURSEJOY.compareTo(access) <= 0) {
			User user = (User) o;
			return read(con, access, user);
		} else if (o instanceof Visit && Access.NURSEJOY.compareTo(access) <= 0) {
			Visit visit = (Visit) o;
			return read(con, access, visit);
		} else if (o instanceof Treatment
				&& Access.NURSEJOY.compareTo(access) <= 0) {
			Treatment treatment = (Treatment) o;
			return read(con, access, treatment);
		} else {
			return -2;
		}
	}
	
	/**
	 * 
	 * @param con
	 * @param access
	 * @param u
	 * @return -1
	 */
	public long read(Connection con, Access access, User u) {
		return -1;
	}
	
	/**
	 * 
	 * @param con
	 * @param access
	 * @param u
	 * @return -1
	 */
	public long read(Connection con, Access access, Visit u) {
		return -1;
	}
	
	/**
	 * 
	 * @param con
	 * @param access
	 * @param u
	 * @return -1
	 */
	public long read(Connection con, Access access, Treatment u) {
		return -1;
	}

	/**
	 * 
	 * @param con
	 * @param access
	 * @param o
	 * @return -1
	 */
	public long update(Connection con, Access access, Object o) {
		if (o == null) {
			return -1;
		} else if (o instanceof User) {
			final User user = (User) o;
			return -1;
		} else if (o instanceof Visit) {
			final Visit visit = (Visit) o;
			return -1;
		} else if (o instanceof Treatment) {
			final Treatment treatment = (Treatment) o;
			return -1;
		} else if (o instanceof Record) {
			final Record record = (Record) o;
			return -1;
		} else {
			return -1;
		}
	}

	public long delete(Connection con, Access access, Object o) {
		if (o == null) {
			return -1;
		} else if (o instanceof User) {
			final User user = (User) o;
			return delete(con, access, user);
		} else if (o instanceof Visit) {
			final Visit visit = (Visit) o;
			return -1;
		} else if (o instanceof Treatment) {
			final Treatment treatment = (Treatment) o;
			return -1;
		} else if (o instanceof Record) {
			final Record record = (Record) o;
			return -1;
		} else {
			return -2;
		}
	}

	public long delete(Connection con, Access access, User u) {
		String remove = "DELETE FROM user WHERE id=" + u.getID() + ";";
		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(remove);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		return u.getID();
	}
	
	public long delete(Connection con, Access access, Visit v) {
		String remove = "DELETE FROM visit WHERE id=" + v.getID() + ";";
		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(remove);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		return v.getID();
	}
	
	public long delete(Connection con, Access access, Treatment t) {
		String remove = "DELETE FROM treatment WHERE id=" + t.getID() + ";";
		try {
			s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			s.execute(remove);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		return t.getID();
	}

	public static void writeLog(String s) {
		new Write(WriteType.SERVER, s);
	}
	
	public String toString(){
		return "Database Controller";
	}

}
